//
//  DataHandle.m
//  HomeIn
//
//  Created by dllo on 15/11/3.
//  Copyright © 2015年 TZP. All rights reserved.
//

#import "DataHandle.h"

@implementation DataHandle

+(instancetype)shareDataBaseHandle
{
    static DataHandle *data=nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        data=[[DataHandle alloc]init];
    });
    return data;
}
static sqlite3 *db=nil;
//创建数据库
-(void)openDB
{
    if (db!=nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *filePath=[array lastObject];
    
    NSString *sqlFilePath = [filePath stringByAppendingPathComponent:@"shushijia.sqlite"];
    NSLog(@"====%@",sqlFilePath);
    int result=sqlite3_open(sqlFilePath.UTF8String, &db);
    if (result==SQLITE_OK) {
        NSLog(@"打开数据库成功");
    }else
    {
        NSLog(@"打开数据库失败");
    }
    
    
}
//创建首页所有收藏表
-(void)createAllTable
{
    NSString *sql=@"CREATE TABLE IF NOT EXISTS jingxuan(number INTEGER PRIMARY KEY AUTOINCREMENT,content_url TEXT,cover_image_url TEXT,short_title TEXT,title TEXT)";
    //    NSString *sql1=@"CREATE TABLE IF NOT EXISTS danping(number INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,description1 TEXT,price TEXT,id INTEGER,cover_image_url TEXT,image_urls TEXT)";
    //    NSString *sql2=@"CREATE TABLE IF NOT EXISTS remenjixuan(number INTEGER PRIMARY KEY AUTOINCREMENT,large_url TEXT,desc TEXT)";
    //    NSString *sql3=@"CREATE TABLE IF NOT EXISTS yijia(number INTEGER PRIMARY KEY AUTOINCREMENT,imgsrc TEXT,title TEXT,digest TEXT,docid TEXT)";
    //    NSString *sql4=@"CREATE TABLE IF NOT EXISTS shuwu(number INTEGER PRIMARY KEY AUTOINCREMENT,content_url TEXT,cover_image_url TEXT,short_title TEXT,title TEXT)";
    //    NSString *sql5=@"CREATE TABLE IF NOT EXISTS chuwu(number INTEGER PRIMARY KEY AUTOINCREMENT,content_url TEXT,cover_image_url TEXT,short_title TEXT,title TEXT)";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    //    int result1 = sqlite3_exec(db, sql1.UTF8String, NULL, NULL, NULL);
    //    int result2 = sqlite3_exec(db, sql2.UTF8String, NULL, NULL, NULL);
    //    int result3 = sqlite3_exec(db, sql3.UTF8String, NULL, NULL, NULL);
    //    int result4 = sqlite3_exec(db, sql4.UTF8String, NULL, NULL, NULL);
    //    int result5 = sqlite3_exec(db, sql5.UTF8String, NULL, NULL, NULL);
    if (result==SQLITE_OK) {
        NSLog(@"创建精选表成功");
    }else
    {
        NSLog(@"创建精选表失败");
    }
    //    if (result1==SQLITE_OK) {
    //        NSLog(@"创建单品表成功");
    //    }else
    //    {
    //        NSLog(@"创建单品表失败");
    //    }
    //    if (result2==SQLITE_OK) {
    //        NSLog(@"创建集选表成功");
    //    }else
    //    {
    //        NSLog(@"创建集选表失败");
    //    }
    //
    //    if (result3==SQLITE_OK) {
    //        NSLog(@"创建易家表成功");
    //    }else
    //    {
    //        NSLog(@"创建易家表失败");
    //    }
    //    if (result4==SQLITE_OK) {
    //        NSLog(@"创建书屋表成功");
    //    }else
    //    {
    //        NSLog(@"创建书屋表失败");
    //    }
    //    if (result5==SQLITE_OK) {
    //        NSLog(@"创建厨务表成功");
    //    }else
    //    {
    //        NSLog(@"创建厨务表失败");
    //    }
    
}

-(void)insertJingxuanCellModelTable:(FirstPageModel *)model
{
    NSString *sql=[NSString stringWithFormat:@"INSERT INTO jingxuan(content_url ,cover_image_url ,short_title ,title) VALUES('%@','%@','%@','%@')",model.content_url,model.cover_image_url,model.short_title,model.title];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result==SQLITE_OK) {
        NSLog(@"收藏精选成功");
    }
    else
    {
        NSLog(@"收藏精选失败");
    }
    
}

//查询精选表
-(NSMutableArray *)selectJingxuan
{
    //存放查询到得model信息
    NSMutableArray *array=[NSMutableArray array];
    NSString *sql=@"SELECT * FROM jingxuan";
    //创建数据库查询跟随指针
    sqlite3_stmt *stmt=nil;
    //查询前准备，主要是将数据库对象，sql语句，跟随指针stmt关联到一起
    //参数1：数据库对象  参数2：sql语句  参数3：设置sql语句长度限制，-1为不限制  参数4：跟随指针  参数5：备用参数
    int  result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result==SQLITE_OK)
    {
        
        //遍历表中每一行
        while (sqlite3_step(stmt)==SQLITE_ROW)
        {
            FirstPageModel *model=[[FirstPageModel alloc]init];
            
            //取出某一行中第一列的数据(编号)
            int number=sqlite3_column_int(stmt, 0);
            //取出某一行的第二,三,四,五,六列
            const unsigned char   *content_url = sqlite3_column_text(stmt, 1);
            const unsigned char   *cover_image_url = sqlite3_column_text(stmt, 2);
            const unsigned char   *short_title= sqlite3_column_text(stmt, 3);
            const unsigned char   *title= sqlite3_column_text(stmt, 4);
            //装成OC型
            NSInteger numInteger = number;
            NSString *content_urlStr=[NSString stringWithUTF8String:(const char *)content_url];
            NSString *cover_image_urlStr=[NSString stringWithUTF8String:(const char *)cover_image_url];
            NSString *short_titleStr=[NSString stringWithUTF8String:(const char *)short_title];
            NSString *titleStr=[NSString stringWithUTF8String:(const char *)title];
            model.content_url=content_urlStr;
            model.cover_image_url=cover_image_urlStr;
            model.short_title=short_titleStr;
            model.title=titleStr;
            [array addObject:model];
            
        }
        
        NSLog(@"查询精选表成功");
    }else
    {
        NSLog(@"查询精选表失败");
    }
    return array;
}
//删除
-(void)deleteJingxuanTable:(NSString *)content_url
{
    NSString *sql=[NSString stringWithFormat:@"DELETE FROM jingxuan WHERE content_url='%@'",content_url];
    int result=sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result==SQLITE_OK) {
        NSLog(@"删除成功");
    }else
    {
        NSLog(@"删除失败");
    }
}

//删除表
-(void)dorpJingxuanTable
{
    NSString *sql=@"DROP TABLE jingxuan";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除精选表成功");
    }else
    {
        NSLog(@"删除精选表失败");
    }
}


//关闭数据库
-(void)closeDB
{
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭成功");
    }else
    {
        NSLog(@"数据库关闭失败");
    }
}


@end
